﻿
using Dapper;

using HBase;
using hCommand; 
using Persistence;
using System;
using System.Collections.Generic;
using System.Linq;
using static Dapper.SqlMapper;

namespace Persistence
{
    public class AbsDooServer : AbsDoServer, IDooServer
    {
        

        public List<WhDooDet> getDetials(long pid)
        {
            String sql = "select * from Wh_Doo_Det  where pid=@pid";
            return readConn().Query<WhDooDet>(sql, new { pid }).ToList();
        }

 

        public List<WhDooDet> GetReceiptOutDetail(DoAddDto dto)
        {
            string sql = "select * from Wh_Doo_Det where 1=1 ";
            if (dto.id != 0)
            {
                sql += " and pid=@receiptId ";
            }
            return readConn().Query<WhDooDet>(sql, new { receiptId = dto.id }).ToList();
        }

        public void DeleteDetailByReceptId(long receiptId)
        {
            string sql = @"DELETE FROM Wh_Doo_Det where pid=@receiptId";
            opConn().Execute(sql, new { receiptId = receiptId });
        }
        public List<CoreStockDet> getCsdList(WhDoo doo) {
            String sql = "select s.* from core_stock_det s join wh_doo_det d on s.doo_det_id=d.id  where d.pid=@id ";
            return readConn().Query<CoreStockDet>(sql, doo).ToList();
        }

        public List<CoreStockDet> getCsdListForSelect(WhDooDet wdd) {
            String sql = "select * from core_stock_det    where item_id=@itemId   and (status=2 or status=9)  and (doo_det_id=0 or doo_det_id=@id) ";
            return readConn().Query<CoreStockDet>(sql, wdd).ToList();
        }
        public List<WhDoo> GetAllList(long receiptOutId)
        {
            String sql = "select * from Wh_doo where id=@receiptOutId ";
            return readConn().Query<WhDoo>(sql, new { receiptOutId = receiptOutId }).ToList();
        }

        /// <summary>
        /// 这个用于打印获取结果集
        /// </summary>
        /// <param name="receiptOutId">出库单主键</param>
        /// <returns></returns>
        public List<DoodetailDto> GetOutDetials(long receiptOutId)
        {
            String sql = @"select A.*,B.name as itemName from Wh_Doo_Det A
                           LEFT OUTER JOIN core_item B ON A.item_Id = B.id  where pid=@receiptOutId";
            return readConn().Query<DoodetailDto>(sql, new { receiptOutId }).ToList();
        }


        public List<WhDoExcel> GetExportList(DoDto dto)
        {
            string sql = @"SELECT i.do_No,d.finsh_Count,it.`code`,it.gg,it.name,it.unit , i.create_Time  FROM `wh_doo` i 
join wh_doo_det d on i.id =d.pid 
join core_item it on it.id=d.item_Id where 1=1";

            if (dto.createBeginTime != DateTime.MinValue) {
                sql += " AND i.create_Time >= @createBeginTime ";
            }
            if (dto.createEndTime != DateTime.MinValue) {
                sql += " AND i.create_Time <= @createEndTime ";
            }
            List<WhDoExcel> WhDoExcelDtos = readConn().Query<WhDoExcel>(sql, 
                new { createBeginTime = dto.createBeginTime,
                    createEndTime = dto.createEndTime.AddDays(1) }).ToList();

            return WhDoExcelDtos;

        }
        public List<WhDooDet> GetListByReceiptId(long receiptId)
        {
            string sql = "select * from Wh_Doo_Det where pid=@receiptId";
            return readConn().Query<WhDooDet>(sql, new { receiptId = receiptId }).ToList();
        }
        public bool UpdateUploadById(long id)
        {
            string sql = @"update Wh_doo set upload=1 where id=@id";
            opConn().Execute(sql, new { id = id });
            return true;
        }

       
       

        public bool UpdateStatusById(long id,int status)
        {
            string sql = @"update Wh_doo set status=@status where id=@id";
            opConn().Execute(sql, new { id, status });
            return true;
        }

       
        public Page<WhDoo> QueryReceiptOutPage(DoDto dto)
        {
            string sql = " select * from Wh_doo  where 1=1 ";
            if (dto.doType > 0) {
                sql += "and  do_type=@doType  ";
            }

            if (!string.IsNullOrEmpty(dto.doNo))
            {
                dto.doNo = "%" + dto.doNo + "%";
                sql += "and do_No like @doNo ";
            }

            if (dto.status != 0)
            {
                sql += "and status = @status ";
            }
            if (dto.stn != 0)
            {
                sql += "and stn = @stn ";
            }
            if (dto.createBeginTime != DateTime.MinValue)
            {
                sql += " AND create_Time >= @createBeginTime ";
            }
            if (dto.createEndTime != DateTime.MinValue)
            {
                sql += " AND create_Time <= @createEndTime ";
            }
            if (!String.IsNullOrEmpty(dto.itemId)) {
                sql += " and id in (select pid from Wh_Doo_Det d where d.item_id=@itemId)";
            }


            dto.createEndTime = dto.createEndTime.AddDays(1);
            //if (dto.finishBeginTime != DateTime.MinValue)
            //{
            //    sql += " AND finsh_Time >= @finishBeginTime ";
            //}
            //if (dto.finishEndTime != DateTime.MinValue)
            //{
            //    sql += " AND finsh_Time <= @finishEndTime ";
            //}

            string orderBy = " create_Time  desc";

            return this.queryPage<WhDoo>(sql, orderBy, dto);
        }
      
    }
}
